package com.friday.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.friday.dao.SignInDao;
import com.friday.dao.SignInHistoryDao;
import com.friday.po.SignIn;
import com.friday.po.SignInHistory;
import com.friday.util.DBUtil;
import com.sun.net.ssl.internal.ssl.Debug;
import com.sun.org.apache.xpath.internal.operations.And;

public class SignInHistoryDaoImpl implements SignInHistoryDao {
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pStmt = null;
	private ResultSet rs = null;
	private DBUtil util = null;


	@Override
	public int insertAllSignInHistory() throws Exception {
		String sql = "insert into signinhistory values(?, ?, ?, ?, ?, ?, ?)";
		int num = 0;
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		conn.setAutoCommit(false);
		
		try {
			SignInDao signInDao = new SignInDaoImpl();
			List<SignIn> list = new ArrayList<SignIn>();
			list = signInDao.querySignInAll();
			SignIn si = new SignIn();
			Iterator<SignIn> iterator = list.iterator();
			
			while(iterator.hasNext()){
				si = (SignIn) iterator.next();
				
				pStmt = conn.prepareStatement(sql);
				pStmt.setString(1, si.getId());
				pStmt.setString(2, si.getEmployee());
				pStmt.setDate(3, si.getDate());
				pStmt.setTime(4,si.getStartTime());
				pStmt.setTime(5, si.getEndTime());
				pStmt.setInt(6, si.getState());
				pStmt.setString(7, si.getDepartment());
			
			num = pStmt.executeUpdate();
			conn.commit();
		}
		} catch (Exception e) {
			conn.rollback();
			throw e;
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public int deleteSignInHistory(String id) throws Exception {
		String sql = " delete from signinhistory where id = ?";
		int num = 0;
		

		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setString(1, id);
			
			num = pStmt.executeUpdate();
			conn.commit();
			
		} catch (Exception e) {
			conn.rollback();
			throw new Exception("SignInHistoryDao delete error!");
			
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public int updateSignInHistory(SignInHistory sih) throws Exception {
		String sql = "update signinhistory set employee = ? ,  date = ? , startTime = ? , endTime = ? ,state = ? , department = ? where id = ?";
		int num;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			
			pStmt = conn.prepareStatement(sql);
			
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, sih.getId());
			pStmt.setString(2, sih.getEmployee());
			pStmt.setDate(3, sih.getDate());
			pStmt.setTime(4,sih.getStartTime());
			pStmt.setTime(5, sih.getEndTime());
			pStmt.setInt(6, sih.getState());
			pStmt.setString(7, sih.getDepartment());
			
			num = pStmt.executeUpdate();
			conn.commit();
			
		} catch (Exception e) {
			conn.rollback();
			throw new Exception("SignInHistoryDao update error!");
			
		}finally{
			util.close(stmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public List<SignInHistory> querySignInHistoryByDepartment(String department)
			throws Exception {
		String sql = "select * from signinhistory where department = '"+ department +"'";
		List<SignInHistory> list = new ArrayList<SignInHistory>();
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		stmt = conn.createStatement();
		
		rs = stmt.executeQuery(sql);
		
		try {
			while(rs.next()){
				SignInHistory sih = new SignInHistory();
				
				sih.setEmployee(rs.getString("employee"));
				sih.setDate(rs.getDate("date"));
				sih.setStartTime(rs.getTime("startTime"));
				sih.setEndTime(rs.getTime("endTime"));
				sih.setState(rs.getInt("state"));
				sih.setDepartment(rs.getString("department"));
				sih.setId(rs.getString("id"));
				
				list.add(sih);
				
			}
		} catch (Exception e) {
			throw new Exception("SignInHistoryDao queryall error!");
			
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
			
		}
		return list;
	}

	@Override
	public List<SignInHistory> querySignInHistoryByDate(Date date) throws Exception {
		String sql = "select * from signinhistory where date = '"+ date +"'";
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		stmt = conn.createStatement();
		List<SignInHistory> list = new ArrayList<SignInHistory>();
		
		rs = stmt.executeQuery(sql);
		
		try{
		while(rs.next()){
			SignInHistory sih = new SignInHistory();
			sih.setEmployee(rs.getString("employee"));
			sih.setDate(rs.getDate("date"));
			sih.setStartTime(rs.getTime("startTime"));
			sih.setEndTime(rs.getTime("endTime"));
			sih.setState(rs.getInt("state"));
			sih.setDepartment(rs.getString("department"));
			sih.setId(rs.getString("id"));
			
			list.add(sih);
		}
		}catch(Exception e){
			throw new Exception("SingInHistoryDao queryByEmployee error!");
			
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
			
		}
		return list;
	}

	@Override
	public List<SignInHistory> querySignInHistoryByEmployee(String employee) throws Exception {
		String sql = "select * from signinhistory where employee = '"+ employee +"'";
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		stmt = conn.createStatement();
		List<SignInHistory> list = new ArrayList<SignInHistory>();
		
		rs = stmt.executeQuery(sql);
		
		try{
		while(rs.next()){
			SignInHistory sih = new SignInHistory();
			
			sih.setEmployee(rs.getString("employee"));
			sih.setDate(rs.getDate("date"));
			sih.setStartTime(rs.getTime("startTime"));
			sih.setEndTime(rs.getTime("endTime"));
			sih.setState(rs.getInt("state"));
			sih.setDepartment(rs.getString("department"));
			sih.setId(rs.getString("id"));
			
			list.add(sih);
		}
		}catch(Exception e){
			throw e;
			
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
			
		}
		return list;
	}

	@Override
	public int deleteSignInHistoryByDate(Date date) throws Exception {
		String sql = " delete from signinhistory where date = ?";
		int num = 0;
		

		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setDate(1, date);
			
			num = pStmt.executeUpdate();
			conn.commit();
			
		} catch (Exception e) {
			conn.rollback();
			throw new Exception("SignInHistoryDao delete error!");
			
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public List<SignInHistory> querySignInHistoryBetweenDate(Date start, Date end)
			throws Exception {
		String sql =  "select * from signinhistory where date between ? and ? order by date";
		List<SignInHistory> list = new ArrayList<SignInHistory>();
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		stmt = conn.createStatement();
		
		pStmt.setDate(1, start);
		pStmt.setDate(2, end);
		
		rs = stmt.executeQuery(sql);
		
		try {
			while(rs.next()){
				SignInHistory sih = new SignInHistory();
				
				sih.setEmployee(rs.getString("employee"));
				sih.setDate(rs.getDate("date"));
				sih.setStartTime(rs.getTime("startTime"));
				sih.setEndTime(rs.getTime("endTime"));
				sih.setState(rs.getInt("state"));
				sih.setDepartment(rs.getString("department"));
				sih.setId(rs.getString("id"));
				
				list.add(sih);
				
			}
		} catch (Exception e) {
			throw new Exception("SignInHistoryDao queryall error!");
			
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
			
		}
		return list;
	}

	@Override
	public int insertSignInHistory(SignInHistory sih) throws Exception {
		String sql = "insert into signinhistory values(?, ?, ?, ?, ?, ?, ?)";
		int num = 0;
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		conn.setAutoCommit(false);
		
		try {
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, sih.getId());
			pStmt.setString(2, sih.getEmployee());
			pStmt.setDate(3, sih.getDate());
			pStmt.setTime(4,sih.getStartTime());
			pStmt.setTime(5, sih.getEndTime());
			pStmt.setInt(6, sih.getState());
			pStmt.setString(7, sih.getDepartment());
			
			num = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw new Exception("SignInHistoryDao insert error!");
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		return num;
	}

	@Override
	public SignInHistory querySignInHistoryByEmployeeAndDate(String employee,
			Date date) throws Exception {
		String sql = "select * from signinhistory where employee = '"+ employee +"' And date ='"+ date +"'";
		SignInHistory sih = null;
		
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		stmt = conn.createStatement();
		
		rs = stmt.executeQuery(sql);
		
		try{
		while(rs.next()){
			sih = new SignInHistory();
			sih.setEmployee(rs.getString("employee"));
			sih.setDate(rs.getDate("date"));
			sih.setStartTime(rs.getTime("startTime"));
			sih.setEndTime(rs.getTime("endTime"));
			sih.setState(rs.getInt("state"));
			sih.setDepartment(rs.getString("department"));
			sih.setId(rs.getString("id"));
			
		}
		}catch(Exception e){
			throw new Exception("SingInHistoryDao queryByEmployee error!");
			
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
			
		}
		return sih;
	}

	@Override
	public List<SignInHistory> querySignInHistoryByDeparmentBetweenDate(
			String depid, Date start, Date end) throws Exception {
		
		String sql =  "select * from signinhistory where date between ? and ? and department = ? order by date";
		List<SignInHistory> list = new ArrayList<SignInHistory>();
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		pStmt = conn.prepareStatement(sql);
		
		pStmt.setDate(1, start);
		pStmt.setDate(2, end);
		pStmt.setString(3, depid);
		
		rs = pStmt.executeQuery();
		
		try {
			while(rs.next()){
				SignInHistory sih = new SignInHistory();
				
				sih.setEmployee(rs.getString("employee"));
				sih.setDate(rs.getDate("date"));
				sih.setStartTime(rs.getTime("startTime"));
				sih.setEndTime(rs.getTime("endTime"));
				sih.setState(rs.getInt("state"));
				sih.setDepartment(rs.getString("department"));
				sih.setId(rs.getString("id"));
				
				list.add(sih);
			}
		} catch (Exception e) {
			throw new Exception("SignInHistoryDao queryall error!");
			
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
			
		}
		return list;
	}

	@Override
	public List<SignInHistory> queryAllHistory() throws Exception {
		String sql = "select * from signinhistory";
		SignInHistory sih = null;
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		stmt = conn.createStatement();
		List<SignInHistory> list = new ArrayList<SignInHistory>();
		
		rs = stmt.executeQuery(sql);
		
		try{
		while(rs.next()){
			sih = new SignInHistory();
			sih.setEmployee(rs.getString("employee"));
			sih.setDate(rs.getDate("date"));
			sih.setStartTime(rs.getTime("startTime"));
			sih.setEndTime(rs.getTime("endTime"));
			sih.setState(rs.getInt("state"));
			sih.setDepartment(rs.getString("department"));
			sih.setId(rs.getString("id"));
			
			list.add(sih);
		}
		}catch(Exception e){
			throw new Exception("SingInHistoryDao queryall error!");
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
		}
		return list;
	}
}
